﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DoeObjects
{
    public class SlimLoanable
    {
        public int LoanableID { get; set; }
        public int TitleId { get; set; }
        public string Title { get; set; }
        public string DoelibsId { get; set; }
        public Status LoanableStatus { get; set; }
        public string Borrower { get; set; }
        public DateTime BorrowDate { get; set; }

        public bool UnavailableFromOwner { get; set; }
        public bool Deleted { get; set; }
        public bool Borrowed { get; set; }
        public bool Reserved { get; set; }
        public int ReservationId { get; set; }



        private const string BASE_SQL = @"
SELECT * FROM Loanable 
INNER JOIN Title 
ON Loanable.TitleId = Title.TitleId
INNER JOIN [User] 
ON Loanable.LoanableOwner = [User].UserId
LEFT JOIN Reservation
ON Reservation.TitleId = Title.TitleId
";



        public static List<SlimLoanable> SlimGetLoanablesByOwner(int ownerID)
        {
            string SQL = BASE_SQL + " WHERE Loanable.LoanableOwner = @ownerID AND Loanable.Deleted != 1";
            List<SlimLoanable> results = new List<SlimLoanable>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.AddWithValue("@ownerID", ownerID);
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    
                    SlimLoanable newSlim = new SlimLoanable();
                    newSlim.LoanableID = Convert.ToInt32(dar["LoanableId"]);
                    newSlim.DoelibsId = dar["Barcode"] as string;
                    newSlim.Title = dar["Title"] as string;
                    newSlim.TitleId = (int)dar["titleId"];
                    newSlim.UnavailableFromOwner = (bool)dar["UnavailableFromOwner"];
                    newSlim.Deleted = (bool)dar["Deleted"];

                    newSlim = SlimLoanable.getCurrentActiveLoanForLoanable(newSlim);
                    
                    if(dar["ReservationId"].ToString() != "")
                    {
                        newSlim.Reserved = true;
                    }

                    newSlim = getStatus(newSlim);
                    

                    results.Add(newSlim);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;
        }



        public static SlimLoanable getCurrentActiveLoanForLoanable(SlimLoanable newSlim)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT FirstName, LastName, BorrowDate FROM [User] INNER JOIN Loan ON Loan.Borrower = [User].UserId WHERE Loan.ReturnDate IS NULL AND loan.loanableId = @loanableId", connection);
            cmd.Parameters.AddWithValue("@loanableId", newSlim.LoanableID);

            try
            {
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    string First = reader["FirstName"] as string;
                    string Last = reader["LastName"] as string;

                    newSlim.Borrower = First + " " + Last;
                    newSlim.BorrowDate = (DateTime)reader["BorrowDate"];
                    newSlim.Borrowed = true;
                }
                reader.Close();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return newSlim;
        }

        public static SlimLoanable getStatus(SlimLoanable newSlim)
        {
            if (newSlim.Deleted)
                newSlim.LoanableStatus = Status.DELETED;
            else if (newSlim.Borrowed && newSlim.UnavailableFromOwner)
                newSlim.LoanableStatus = Status.RECALLED;
            else if (!newSlim.Borrowed && newSlim.UnavailableFromOwner)
                newSlim.LoanableStatus = Status.UNAVAILABLE_FROM_OWNER;
            else if (newSlim.Borrowed)
                newSlim.LoanableStatus = Status.BORROWED;
            else if (newSlim.Reserved)
                newSlim.LoanableStatus = Status.RESERVED;
            else if (newSlim.LoanableID == 0)
                newSlim.LoanableStatus = Status.UNKNOWN;
            else
                newSlim.LoanableStatus = Status.AVAILABLE;

            return newSlim;
        }



        public static List<SlimLoanable> getByCategory(int locationCategoryId)
        {
            List<SlimLoanable> results = new List<SlimLoanable>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            //Changed for delete
            SqlCommand cmd = new SqlCommand("SELECT Title.Title, Title.TitleId,Loanable.LoanableId, Loanable.Barcode FROM LoanableCategory INNER JOIN Loanable ON LoanableCategory.LoanableCategoryId = Loanable.LoanableCategoryId INNER JOIN Title ON Loanable.TitleId = Title.TitleId WHERE Loanable.LoanableCategoryId = @id AND Loanable.Deleted != 1;", con);
            cmd.Parameters.AddWithValue("@id", locationCategoryId);

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    SlimLoanable newSlim = new SlimLoanable();
                    newSlim.LoanableID = Convert.ToInt32(dar["LoanableId"]);
                    newSlim.Title = dar["Title"] as string;
                    newSlim.TitleId = (int)dar["TitleId"];
                    newSlim.DoelibsId = dar["Barcode"] as string;
                    newSlim = getCurrentActiveLoanForLoanable(newSlim);
                    newSlim = getStatus(newSlim);

                    results.Add(newSlim);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;
        }
    }
}
